﻿DECLARE @TotalPontos DECIMAL(10, 4), 
		@TotalDias INT,
		@PontosFeitos DECIMAL(10, 4),
		@IdAcaoMover INT,
		@IdQuadro INT = 84,
		@DataInicio DATETIME,
		@DataFim DATETIME,
		@PontosPorDia DECIMAL(10, 4),
		@UltimoStatus INT = 2 -- Subtraí 1


SELECT @IdAcaoMover = Id FROM LogAcao WHERE Mnemonico = 'TAREFA_MOVER'
SELECT @DataInicio = CONVERT(VARCHAR(10), DataInicio, 101), @DataFim = CONVERT(VARCHAR(10), DataFim, 101) FROM Quadro WHERE Id = @IdQuadro
SELECT @TotalPontos = COUNT(*) * @UltimoStatus FROM Tarefa WHERE IdQuadro = @IdQuadro

SELECT CONVERT(VARCHAR(10), l.DataCadastro, 101) AS Data, SUM(CAST(ValorNovo AS DECIMAL(10, 4)) - 1) AS Executado 
INTO #Log
FROM LogTarefa l
INNER JOIN Tarefa t
ON t.Id = l.IdTarefa
WHERE t.IdQuadro = @IdQuadro
AND l.IdLogAcao = @IdAcaoMover
AND t.Excluido = 0 AND l.Excluido = 0
GROUP BY CONVERT(VARCHAR(10), l.DataCadastro, 101)

SET @TotalDias = DATEDIFF(d, @DataInicio, @DataFim)
SET @PontosPorDia =  @TotalPontos / @TotalDias

CREATE Table #Burndown(Data DATETIME, Planejado DECIMAL(10, 4), Executado DECIMAL(10, 4))

DECLARE @Dia INT = 0, @Valor DECIMAL(10, 4) = 0, @Data DATETIME = @DataInicio
WHILE @Data <= @DataFim 
BEGIN
		

	IF @Data > GETDATE() 
	BEGIN
		SET @Valor = NULL
	END 
	ELSE 
	BEGIN
		IF (SELECT Executado FROM #Log WHERE Data = @Data) IS NOT NULL 
		BEGIN
			SELECT @Valor = Executado FROM #Log WHERE Data = @Data
		END
	END

	INSERT INTO #Burndown
	VALUES(@Data, @TotalPontos - @PontosPorDia * @Dia, @TotalPontos - @Valor)

	SET @Dia = @Dia + 1
	SET @Data = DATEADD(d, @Dia, @DataInicio)
END

SELECT Data, Planejado, Executado AS Queimado FROM #Burndown

DROP TABLE #Burndown
DROP TABLE #Log